﻿using DevExpress.Spreadsheet;
using DevExpress.XtraEditors;
using FlyHorse.LIMS.BLL;
using FlyHorse.LIMS.Model;
using FlyHorse.LIMS.WinApp.LanguageResource;
using FlyHorse.LIMS.WinApp.Model;
using FlyHorse.LIMS.WinApp.Utils;
using FlyHorse.LIMS.WinApp.Utils.MonthReportUtils;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;


namespace FlyHorse.LIMS.WinApp.DataAnalyzeFroms.MonthlyReport
{
    public partial class MonthlyReportMainForm : Form
    {
        ReportRawBll rawReportBll = new ReportRawBll();
        ReportSemiBll reportSemiBll = new ReportSemiBll();
        StandardRawBll standardRawBll = new StandardRawBll();
        StandardSemiBll standardSemiBll = new StandardSemiBll();
        ToolBll toolBll = new ToolBll();
        OJCB4Entities db = new OJCB4Entities();
        public MonthlyReportMainForm()
        {
            InitializeComponent();
        }

        private void MonthlyReportMainForm_Load(object sender, EventArgs e)
        {
            this.barEditItem_startDate.EditValue = "2017/03/01";
            this.barEditItem_endDate.EditValue = "2017/03/31";
        }
        private void barButtonItem_Sbmit_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            InitAllReport();
        }

        private void InitAllReport()
        {

            DateTime startDate;
            DateTime endDate;
            try
            {

                startDate = Convert.ToDateTime(this.barEditItem_startDate.EditValue);
                endDate = Convert.ToDateTime(this.barEditItem_endDate.EditValue);
            }
            catch (Exception)
            {
                XtraMessageBox.Show(ResourceOverall.DateInputErr);
                return;
            }
            finally
            {

            }
            // InitRawReport(startDate, endDate);
            DoDataExportDataFromDataBase(startDate, endDate);
        }
        #region //原材料报告
        private void InitRawReport(DateTime startDate, DateTime endDate)
        {
            spreadsheetControl1.BeginUpdate();
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1.Name = "1)RawMaterial";
            RawDecorateAll(startDate, endDate);
            //设置检验项目对应的行数

            //List<Good> goodsList = toolBll.GetRawGoodList();
            List<Good> goodsList = db.Goods.Where(m => m.GoodsType.GoodsTypeName == "原材料").ToList();
            List<RawMonthReport> reportDict = new List<RawMonthReport>();
            int startRow = 1;
            int rowsWhitoutData = 10;
            for (int i = 0; i < goodsList.Count; i++)
            {
                string goodsID = goodsList[i].GoodsID;
                //var modelsRaw = rawReportBll.LoadEntitiesAsNoTracking(m => m.GoodsID == goodsID && m.SendDate >= startDate && m.SendDate <= endDate).ToList();
                var modelsRaw = db.ReportRaws.Where(m => m.GoodsID == goodsID && m.SendDate >= startDate && m.SendDate <= endDate).ToList();
                if (modelsRaw.Count > 0)
                {
                    RawMonthReport rawMonthReport = new RawMonthReport();
                    rawMonthReport.startRowIndex = startRow;
                    rawMonthReport.endRowIndex = startRow + rowsWhitoutData + modelsRaw.Count;
                    //rawMonthReport.standardRawList = standardRawBll.LoadEntitiesAsNoTracking(m => m.GoodsID == goodsID).ToList();
                    rawMonthReport.standardRawList = db.StandardRaws.Where(m => m.GoodsID == goodsID).ToList();
                    rawMonthReport.reportList = TransformReport.TransformRawReport(modelsRaw);
                    rawMonthReport.goodsName = goodsList[i].GoodsName;
                    reportDict.Add(rawMonthReport);
                    startRow += modelsRaw.Count + rowsWhitoutData + 2;

                }
            }
            //开始遍历dictionary 
            for (int i = 0; i < reportDict.Count; i++)
            {
                //1写标题,样品名称
                worksheet1[reportDict[i].startRowIndex, 1].Value = (i + 1) + ")" + reportDict[i].goodsName;
                //2写检验项目行
                RawWriteRawTitle(reportDict[i].startRowIndex + 1);
                //3写上一年的平均值
                RawWriteLastYearAvg(reportDict[i], startDate);
                //4标准 下线
                RawWriteStandard(reportDict[i], reportDict[i].standardRawList);
                //报告数据
                RawWriteRawReportDetail(reportDict[i]);
                //count
                RawWriteCount(reportDict[i]);
                //avg
                RawWriteAvg(reportDict[i]);
                //max
                RawWriteMax(reportDict[i]);
                //min
                RawWriteMin(reportDict[i]);
                //STDEV
                RawWriteSTDEV(reportDict[i]);
                //装饰  边框
                RawDecorateOnce(reportDict[i]);
            }
            spreadsheetControl1.EndUpdate();
        }

        private void RawDecorateAll(DateTime startDate, DateTime endDate)
        {
            //1. Analysis result of CB raw materials
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[0, 0].Value = "1. Analysis result of CB raw materials(" + startDate.ToShortDateString() + "--" + endDate.ToShortDateString() + ")";
            worksheet1[0, 0].Font.Size = 14;
            worksheet1[0, 0].Font.Bold = true;
            RowCollection rows = worksheet1.Rows;
            Row firstRow_byIndex = rows[0];
            firstRow_byIndex.RowHeight = 140;

            //第一列的列宽
            worksheet1.Columns[0].ColumnWidth = 80;
            worksheet1.Columns[1].ColumnWidth = 80;
            worksheet1.Columns[2].ColumnWidth = 280;
        }
        private void RawDecorateOnce(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            //边框
            Range range = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 1, 12, monthReport.endRowIndex);
            range.SetInsideBorders(Color.Black, BorderLineStyle.Dashed);
            range.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);

            worksheet1.Rows[monthReport.startRowIndex + 1].RowHeight = 140;


        }
        private void RawWriteRawTitle(int row)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[row, 1].Value = "NO";
            worksheet1[row, 2].Value = "AnalysisDate";
            worksheet1[row, 3].Value = "ProductBy";
            worksheet1[row, 4].Value = "SP/Gr";
            worksheet1[row, 5].Value = "Mositure";
            worksheet1[row, 6].Value = "BMCI";
            worksheet1[row, 7].Value = "Englar Viscosity";
            worksheet1[row, 8].Value = "C";
            worksheet1[row, 9].Value = "H";
            worksheet1[row, 10].Value = "N";
            worksheet1[row, 11].Value = "O";
            worksheet1[row, 12].Value = "S";
            Range range = worksheet1.Range.FromLTRB(1, row, 12, row);
            Formatting rangeFormatting = range.BeginUpdateFormatting();
            rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
            // Specify text alignment in cells.
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            // End updating of the range formatting.
            range.EndUpdateFormatting(rangeFormatting);

        }
        private void RawWriteLastYearAvg(RawMonthReport monthReport, DateTime startDate)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            int year = startDate.Year - 1;
            worksheet1[monthReport.startRowIndex + 2, 1].Value = year;
            worksheet1[monthReport.startRowIndex + 2, 3].Value = "AVG";
            Range rangeYear = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 2, 2, monthReport.startRowIndex + 2);
            worksheet1.MergeCells(rangeYear);

            //OJCB4Entities db = (OJCB4Entities)standardRawBll.entity;
            //DbRawSqlQuery<double> result2 = db.Database.SqlQuery<double>("SELECT count(*) FROM test.student");
            //背景颜色
            Range range = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 2, 12, monthReport.startRowIndex + 2);
            Formatting rangeFormatting = range.BeginUpdateFormatting();
            rangeFormatting.Fill.BackgroundColor = Color.FromArgb(204, 255, 204);
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

            range.EndUpdateFormatting(rangeFormatting);

        }
        private void RawWriteStandard(RawMonthReport monthReport, List<StandardRaw> standardList)
        {
            int row = monthReport.startRowIndex + 3;
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[row, 3].Value = "LSL";
            worksheet1[row, 3].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            worksheet1[row, 3].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheet1[row + 1, 3].Value = "USL";
            worksheet1[row + 1, 3].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            worksheet1[row + 1, 3].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            //Spec'
            worksheet1[row, 1].Value = "Spec'";
            Range rangeSpec = worksheet1.Range.FromLTRB(1, row, 2, row + 1);
            worksheet1.MergeCells(rangeSpec);
            Formatting rangeFormatting = rangeSpec.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeSpec.EndUpdateFormatting(rangeFormatting);
            //填写标准  //原材料检验项目    SP/Gr  比重   Moisture水分      BMCI  芳烃指数         Englar Viscosity  恩氏黏度      
            //List<TestItem> rawTestItemList = FlyHorse.LIMS.WinApp.Utils.MonthReportUtils.MonthReportBaseSetings.GetRawTestItemList();
            List<TestItem> rawTestItemList = db.TestItems.Where(m => m.TestItemID == CommonData.TestItem_SP_ID || m.TestItemID == CommonData.TestItem_Mositure_ID || m.TestItemID == CommonData.TestItem_BMCI_ID || m.TestItemID == CommonData.TestItem_EnglarViscosity_ID).ToList(); ;
            for (int i = 0; i < rawTestItemList.Count; i++)
            {
                RawWriteStandartDetail(rawTestItemList[i].TestItemID, standardList, row, i);
            }

        }
        private void RawWriteStandartDetail(string id, List<StandardRaw> standardList, int row, int column)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            StandardRaw standard = standardList.Where(m => m.TestItemID == id).FirstOrDefault();
            if (standard != null)
            {
                if (!String.IsNullOrEmpty(standard.MinValue))
                {
                    worksheet1[row, 4 + column].Value = Convert.ToDouble(standard.MinValue);
                }
                if (!String.IsNullOrEmpty(standard.MaxValue))
                {
                    worksheet1[row + 1, 4 + column].Value = Convert.ToDouble(standard.MaxValue);
                }
            }


        }
        private void RawWriteRawReportDetail(RawMonthReport monthReport)
        {
            for (int i = 0; i < monthReport.reportList.Count; i++)
            {
                IWorkbook workbook = spreadsheetControl1.Document;
                Worksheet worksheet1 = workbook.Worksheets[0];
                worksheet1[monthReport.startRowIndex + 5 + i, 1].Value = i + 1;   //NO
                worksheet1[monthReport.startRowIndex + 5 + i, 2].Value = monthReport.reportList[i].SendDate;   //SendDate
                worksheet1[monthReport.startRowIndex + 5 + i, 3].Value = monthReport.reportList[i].ProductionUnit;   //ProductBy
                if (monthReport.reportList[i].BiZhong != null)
                {
                    worksheet1[monthReport.startRowIndex + 5 + i, 4].Value = Convert.ToDouble(monthReport.reportList[i].BiZhong);   //SP/Gr 
                }
                if (monthReport.reportList[i].ShuiFen != null)
                {
                    worksheet1[monthReport.startRowIndex + 5 + i, 5].Value = Convert.ToDouble(monthReport.reportList[i].ShuiFen);
                }
                if (monthReport.reportList[i].BMCI != null)
                {
                    worksheet1[monthReport.startRowIndex + 5 + i, 6].Value = Convert.ToDouble(monthReport.reportList[i].BMCI);
                }
                if (monthReport.reportList[i].EnShiNianDu != null)
                {
                    worksheet1[monthReport.startRowIndex + 5 + i, 7].Value = Convert.ToDouble(monthReport.reportList[i].EnShiNianDu);
                }
            }
        }
        private void RawWriteCount(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 1, 1].Value = "Count";

            Range rangeCount = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 1, 2, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 1);
            worksheet1.MergeCells(rangeCount);

            Formatting rangeFormatting = rangeCount.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeCount.EndUpdateFormatting(rangeFormatting);

            for (int i = 0; i < 4; i++)
            {
                Cell cellLT = worksheet1[monthReport.startRowIndex + 5, i + 4];
                Cell cellRB = worksheet1[monthReport.startRowIndex + 4 + monthReport.reportList.Count, i + 4];
                // string aa = cellLT.GetReferenceA1();
                string formula = "COUNTIF(" + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + ",\">0\")";

                worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 1, i + 4].Formula = formula;
            }
        }
        private void RawWriteAvg(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 2, 1].Value = "Avg";

            Range rangeAvg = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 2, 2, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 2);
            worksheet1.MergeCells(rangeAvg);

            Formatting rangeFormatting = rangeAvg.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeAvg.EndUpdateFormatting(rangeFormatting);

            for (int i = 0; i < 4; i++)
            {
                Cell cellLT = worksheet1[monthReport.startRowIndex + 5, i + 4];
                Cell cellRB = worksheet1[monthReport.startRowIndex + 4 + monthReport.reportList.Count, i + 4];
                //=IFERROR(AVERAGEIF(E151:E210,">0",E151:E210),"")   =IFERROR(AVERAGEIF(E7:E9,\">0\",E7:E9,\"\")
                string formula = @"=IFERROR(AVERAGEIF(" + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + ",\">0\"," + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + "),\"\")";
                worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 2, i + 4].Formula = formula;
            }
        }
        private void RawWriteMax(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 3, 1].Value = "Max";

            Range rangeMax = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 3, 2, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 3);
            worksheet1.MergeCells(rangeMax);

            Formatting rangeFormatting = rangeMax.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeMax.EndUpdateFormatting(rangeFormatting);

            for (int i = 0; i < 4; i++)
            {
                Cell cellLT = worksheet1[monthReport.startRowIndex + 5, i + 4];
                Cell cellRB = worksheet1[monthReport.startRowIndex + 4 + monthReport.reportList.Count, i + 4];
                string formula = "=MAX(" + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + ")";
                worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 3, i + 4].Formula = formula;
            }

        }
        private void RawWriteMin(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 4, 1].Value = "Min";

            Range rangeMin = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 4, 2, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 4);
            worksheet1.MergeCells(rangeMin);

            Formatting rangeFormatting = rangeMin.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeMin.EndUpdateFormatting(rangeFormatting);


            for (int i = 0; i < 4; i++)
            {
                Cell cellLT = worksheet1[monthReport.startRowIndex + 5, i + 4];
                Cell cellRB = worksheet1[monthReport.startRowIndex + 4 + monthReport.reportList.Count, i + 4];
                string formula = "=Min(" + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + ")";
                worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 4, i + 4].Formula = formula;
            }

        }
        private void RawWriteSTDEV(RawMonthReport monthReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 5, 1].Value = "Std";

            Range rangeStd = worksheet1.Range.FromLTRB(1, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 5, 2, monthReport.startRowIndex + 5 + monthReport.reportList.Count + 5);
            worksheet1.MergeCells(rangeStd);

            Formatting rangeFormatting = rangeStd.BeginUpdateFormatting();
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            rangeStd.EndUpdateFormatting(rangeFormatting);


            for (int i = 0; i < 4; i++)
            {
                Cell cellLT = worksheet1[monthReport.startRowIndex + 5, i + 4];
                Cell cellRB = worksheet1[monthReport.startRowIndex + 4 + monthReport.reportList.Count, i + 4];
                string formula = "=STDEV(" + cellLT.GetReferenceA1() + ":" + cellRB.GetReferenceA1() + ")";
                worksheet1[monthReport.startRowIndex + 5 + monthReport.reportList.Count + 5, i + 4].Formula = formula;
            }

        }
        private void RawSetConditional()
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheet1 = workbook.Worksheets[0];
            // Create the rule to identify values below 7 and above 19 in cells F2 through F15.  
            RangeConditionalFormatting cfRule = worksheet1.ConditionalFormattings.AddRangeConditionalFormatting(worksheet1.Range["$F$2:$F$15"], ConditionalFormattingRangeCondition.Outside, "7", "19");
            // Specify formatting options to be applied to cells if the condition is true.
            // Set the background color to yellow.
            cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
            // Set the font color to red.
            cfRule.Formatting.Font.Color = Color.Red;

        }
        #endregion


        #region DO报告
        private void InitDoReport(DateTime startDate, DateTime endDate)
        {
            spreadsheetControl1.BeginUpdate();
            IWorkbook workbook = spreadsheetControl1.Document;
            workbook.Worksheets.Add("DO");
            workbook.Worksheets.Add("DO_Data");
            Worksheet worksheetDO = workbook.Worksheets["DO"];
            //大写标题   2. Analysis result of process product
            worksheetDO[0, 0].Value = "2. Analysis result of process product";


            //List<Good> goodsList = toolBll.GetSemiGoodList();
            List<Good> goodsList = db.Goods.Where(m => m.GoodsType.GoodsTypeName == "产品").ToList();
            List<DoMonthReport> reportDict = new List<DoMonthReport>();

            //List<ReportSemi> modelsSemiData = reportSemiBll.LoadEntitiesAsNoTracking(m => m.SamplingPlace.SamplingPlaceID == CommonData.TestItem_Do_ID && m.SendDate >= startDate && m.SendDate <= endDate).OrderBy(m => m.SendDate).ToList();
            List<ReportSemi> modelsSemiData = db.ReportSemis.Where(m => m.SamplingPlace.SamplingPlaceID == CommonData.SamplingPlace_Do_ID && m.SendDate >= startDate && m.SendDate <= endDate).OrderBy(m => m.SendDate).ToList();

            //string sql = "";
            //DbParameter[] args = new DbParameter[] { new SqlParameter { ParameterName = "DOid", Value = "5" }, new SqlParameter { ParameterName = "startDate", Value = startDate }, new SqlParameter { ParameterName = "endDate", Value = endDate } };
            //List<ReportSemi> modelsSemiData1 = db.Set<ReportSemi>().SqlQuery("slec", args).ToList();
            List<StandardSemi> standardList = db.StandardSemis.Where(m => m.SamplingPlaceID == CommonData.SamplingPlace_Do_ID).ToList();

            int startRow = 1;
            int titelRow = 2;
            int dataRow = 16;
            int curveRow = 30;
            int otherRow = 30;
            int reportRow = titelRow + dataRow + curveRow + otherRow;
            int dataStartRow = 0;
            for (int i = 0; i < goodsList.Count; i++)
            {
                string goodsID = goodsList[i].GoodsID;
                var modelsSemi = modelsSemiData.Where(m => m.GoodsID == goodsID).ToList();
                if (modelsSemi.Count > 0)
                {
                    DoMonthReport semiMonthReport = new DoMonthReport();
                    semiMonthReport.startRowIndex = startRow;
                    semiMonthReport.endRowIndex = startRow + reportRow;
                    semiMonthReport.standardSemiList = standardList.Where(m => m.GoodsID == goodsID).ToList();
                    semiMonthReport.reportList = TransformReport.TransformSemiReport(modelsSemi);
                    semiMonthReport.goodsName = goodsList[i].GoodsName;
                    semiMonthReport.goodsID = goodsID;
                    semiMonthReport.dataStartRow = dataStartRow;
                    semiMonthReport.dataEndRow = dataStartRow + semiMonthReport.reportList.Count + 10;
                    dataStartRow = dataStartRow + (semiMonthReport.reportList.Count + 10);
                    reportDict.Add(semiMonthReport);
                    startRow += reportRow + 2;

                }
            }
            
            //开始遍历dictionary 
            for (int i = 0; i < reportDict.Count; i++)
            {
                //1写标题
                //DoWriteTitel(reportDict[i], i, startDate, endDate);
                //DoWriteDoData(reportDict[i]);
                //DoWriteStandard(reportDict[i]);
                //DoWriteBeforData(reportDict[i], startDate, endDate);

            }
            spreadsheetControl1.EndUpdate();

        }

        /// <summary>
        /// 到出DO数据到excel
        /// </summary>
        /// <param name="semiReport"></param>
        private void DoWriteDoData(DoMonthReport semiReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheetDO = workbook.Worksheets["DO"];
            Worksheet worksheetDO_Data = workbook.Worksheets["DO_Data"];
            //写表头
            Dictionary<int, string> titelDict = CommonData.dictForMonthlyReportDo_Data;
            foreach (var item in titelDict)
            {
                worksheetDO_Data[semiReport.dataStartRow, item.Key].Value = item.Value;
            }
            #region 写数据

            //写数据
            for (int i = 0; i < semiReport.reportList.Count; i++)
            {
                worksheetDO_Data[semiReport.dataStartRow + 1 + i, 0].Value = semiReport.reportList[i].SendDate;
                worksheetDO_Data[semiReport.dataStartRow + 1 + i, 1].Value = semiReport.reportList[i].GoodsName;
                worksheetDO_Data[semiReport.dataStartRow + 1 + i, 2].Value = semiReport.reportList[i].SamplingPlaceName;
                if (semiReport.reportList[i].IA != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 3].Value = Convert.ToDouble(semiReport.reportList[i].IA);
                }
                if (semiReport.reportList[i].DBP != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 4].Value = Convert.ToDouble(semiReport.reportList[i].DBP);
                }
                if (semiReport.reportList[i].IPH_AVG != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 5].Value = Convert.ToDouble(semiReport.reportList[i].IPH_AVG);
                }
                if (semiReport.reportList[i].IPH_High != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 6].Value = Convert.ToDouble(semiReport.reportList[i].IPH_High);
                }
                if (semiReport.reportList[i].IPH_Low != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 7].Value = Convert.ToDouble(semiReport.reportList[i].IPH_Low);
                }
                if (semiReport.reportList[i].Fines != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 8].Value = Convert.ToDouble(semiReport.reportList[i].Fines);
                }
                if (semiReport.reportList[i].HL != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 9].Value = Convert.ToDouble(semiReport.reportList[i].HL);
                }
                if (semiReport.reportList[i].Sieve35 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 10].Value = Convert.ToDouble(semiReport.reportList[i].Sieve35);
                }
                if (semiReport.reportList[i].Sieve100 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 11].Value = Convert.ToDouble(semiReport.reportList[i].Sieve100);
                }
                if (semiReport.reportList[i].Sieve325 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 12].Value = Convert.ToDouble(semiReport.reportList[i].Sieve325);
                }
                if (semiReport.reportList[i].LT != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 13].Value = Convert.ToDouble(semiReport.reportList[i].LT);
                }
                if (semiReport.reportList[i].PD != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 14].Value = Convert.ToDouble(semiReport.reportList[i].PD);
                }
                if (semiReport.reportList[i].PH != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 15].Value = Convert.ToDouble(semiReport.reportList[i].PH);
                }
                if (semiReport.reportList[i].PSD10 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 16].Value = Convert.ToDouble(semiReport.reportList[i].PSD10);
                }
                if (semiReport.reportList[i].PSD18 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 17].Value = Convert.ToDouble(semiReport.reportList[i].PSD18);
                }
                if (semiReport.reportList[i].PSD35 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 18].Value = Convert.ToDouble(semiReport.reportList[i].PSD35);
                }
                if (semiReport.reportList[i].PSD60 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 19].Value = Convert.ToDouble(semiReport.reportList[i].PSD60);
                }
                if (semiReport.reportList[i].PSD120 != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 20].Value = Convert.ToDouble(semiReport.reportList[i].PSD120);
                }
                if (semiReport.reportList[i].PSDpan != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 21].Value = Convert.ToDouble(semiReport.reportList[i].PSDpan);
                }
                if (semiReport.reportList[i].Tint != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 22].Value = Convert.ToDouble(semiReport.reportList[i].Tint);
                }
                if (semiReport.reportList[i].CDBP != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 23].Value = Convert.ToDouble(semiReport.reportList[i].CDBP);
                }
                if (semiReport.reportList[i].N2SA != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 24].Value = Convert.ToDouble(semiReport.reportList[i].N2SA);
                }
                if (semiReport.reportList[i].Ash != null)
                {
                    worksheetDO_Data[semiReport.dataStartRow + 1 + i, 25].Value = Convert.ToDouble(semiReport.reportList[i].Ash);
                }
            }
            #endregion


            #region 合并单元格
            Range rangeLSL = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 9, 2, semiReport.dataEndRow - 9);
            Range rangeUSL = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 8, 2, semiReport.dataEndRow - 8);
            Range rangeCount = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 7, 2, semiReport.dataEndRow - 7);
            Range rangeAVG = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 6, 2, semiReport.dataEndRow - 6);
            Range rangeStd = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 5, 2, semiReport.dataEndRow - 5);
            Range rangeMin = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 4, 2, semiReport.dataEndRow - 4);
            Range rangeMax = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 3, 2, semiReport.dataEndRow - 3);
            Range rangeCPK = worksheetDO_Data.Range.FromLTRB(0, semiReport.dataEndRow - 2, 2, semiReport.dataEndRow - 2);
            worksheetDO_Data.MergeCells(rangeLSL);
            worksheetDO_Data.MergeCells(rangeUSL);
            worksheetDO_Data.MergeCells(rangeCount);
            worksheetDO_Data.MergeCells(rangeAVG);
            worksheetDO_Data.MergeCells(rangeStd);
            worksheetDO_Data.MergeCells(rangeMin);
            worksheetDO_Data.MergeCells(rangeMax);
            worksheetDO_Data.MergeCells(rangeCPK);
            worksheetDO_Data[semiReport.dataEndRow - 9, 0].Value = "LSL";
            worksheetDO_Data[semiReport.dataEndRow - 8, 0].Value = "USL";
            worksheetDO_Data[semiReport.dataEndRow - 7, 0].Value = "Count";
            worksheetDO_Data[semiReport.dataEndRow - 6, 0].Value = "AVG";
            worksheetDO_Data[semiReport.dataEndRow - 5, 0].Value = "Std";
            worksheetDO_Data[semiReport.dataEndRow - 4, 0].Value = "Min";
            worksheetDO_Data[semiReport.dataEndRow - 3, 0].Value = "Max";
            worksheetDO_Data[semiReport.dataEndRow - 2, 0].Value = "CPK";
            #endregion


            for (int i = 3; i < 26; i++)
            {
                //下限
                DoDataWriteStandard(i, worksheetDO_Data, semiReport.dataEndRow - 9, semiReport);
                Cell cellLT = worksheetDO_Data[semiReport.dataStartRow + 1, i];
                Cell cellRB = worksheetDO_Data[semiReport.dataEndRow - 10, i];

                //=COUNTIF(E7:E40,\">0\")
                string formulaCount = "COUNTIF(" + cellLT.Worksheet.Name + "!" + cellLT.GetReferenceA1() + ":" + cellRB.Worksheet.Name + "!" + cellRB.GetReferenceA1() + ",\">0\")";
                worksheetDO_Data[semiReport.dataEndRow - 7, i].Formula = formulaCount;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 4, i + 1].Formula = formulaCount;
                }
                //=IFERROR(AVERAGEIF(E7:E40,">0",E7:E40),"")
                string formulaAvg = "AVERAGEIF(" + cellLT.Worksheet.Name + "!" + cellLT.GetReferenceA1() + ":" + cellLT.Worksheet.Name + "!" + cellRB.GetReferenceA1() + ",\">0\")";
                worksheetDO_Data[semiReport.dataEndRow - 6, i].Formula = formulaAvg;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 5, i + 1].Formula = formulaAvg;
                }
                //std    =IFERROR(STDEV(IF((E7:E40>0),E7:E40)),"")    STDEV(D2:D142
                string formulaStd = "STDEV(" + cellLT.Worksheet.Name + "!" + cellLT.GetReferenceA1() + ":" + cellLT.Worksheet.Name + "!" + cellRB.GetReferenceA1() + ")";
                worksheetDO_Data[semiReport.dataEndRow - 5, i].Formula = formulaStd;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 6, i + 1].Formula = formulaStd;
                }
                //min  =MIN(IF((E7:E40>0),E7:E40))
                string formulaMin = "MIN(" + cellLT.Worksheet.Name + "!" + cellLT.GetReferenceA1() + ":" + cellLT.Worksheet.Name + "!" + cellRB.GetReferenceA1() + ")";
                worksheetDO_Data[semiReport.dataEndRow - 4, i].Formula = formulaMin;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 7, i + 1].Formula = formulaMin;
                }
                //MAx  =max(IF((E7:E40>0),E7:E40))
                string formulaMax = "MAX(" + cellLT.Worksheet.Name + "!" + cellLT.GetReferenceA1() + ":" + cellLT.Worksheet.Name + "!" + cellRB.GetReferenceA1() + ")";
                worksheetDO_Data[semiReport.dataEndRow - 3, i].Formula = formulaMax;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 8, i + 1].Formula = formulaMax;
                }                                                                                   //=IFERROR(IF(AND(E109="",E110=""),"",MIN((E109-E118)/3/E119,(E118-E110)/3/E119)),"")     E109上限  E110 下线   118 avg  119西格玛
                Cell cellUSL = worksheetDO_Data[semiReport.dataEndRow - 8, i];
                Cell cellLSL = worksheetDO_Data[semiReport.dataEndRow - 9, i];
                Cell cellAvg = worksheetDO_Data[semiReport.dataEndRow - 6, i];
                Cell cellSigama = worksheetDO_Data[semiReport.dataEndRow - 5, i];
                //MIN((E109-E118)/3/E119,    (E118-E110)/3/E119)),"")                                                     
                string formulaCPK = "IFERROR(IF(AND(" + cellLT.Worksheet.Name + "!" + cellUSL.GetReferenceA1() + " = \"\"," + cellLT.Worksheet.Name + "!" + cellLSL.GetReferenceA1() + "=\"\"),\"\",MIN((" + cellLT.Worksheet.Name + "!" + cellUSL.GetReferenceA1() + "-" + cellLT.Worksheet.Name + "!" + cellAvg.GetReferenceA1() + ")" + "/3/" + cellLT.Worksheet.Name + "!" + cellSigama.GetReferenceA1() + ",(" + cellLT.Worksheet.Name + "!" + cellAvg.GetReferenceA1() + "-" + cellLT.Worksheet.Name + "!" + cellLSL.GetReferenceA1() + ")/3/" + cellLT.Worksheet.Name + "!" + cellSigama.GetReferenceA1() + ")),\"\")";
                worksheetDO_Data[semiReport.dataEndRow - 2, i].Formula = formulaCPK;
                if (i <= 12)
                {
                    worksheetDO[semiReport.startRowIndex + 9, i + 1].Formula = formulaCPK;
                }
            }


        }

        /// <summary>
        /// DO 图表页面 每个规格的标题
        /// </summary>
        /// <param name="semiReport"></param>
        /// <param name="NO"></param>
        private void DoWriteTitel(DoMonthReport semiReport, int NO, DateTime startDate, DateTime endDate)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheetDO = workbook.Worksheets["DO"];
            Worksheet worksheetDO_Data = workbook.Worksheets["DO_Data"];
            // 第一行  Unit    Grade   
            worksheetDO[semiReport.startRowIndex, 0].Value = "Unit";
            worksheetDO[semiReport.startRowIndex, 1].Value = "Grade";
            worksheetDO[semiReport.startRowIndex, 2].Value = "Month";
            worksheetDO[semiReport.startRowIndex, 3].Value = "Category";
            worksheetDO[semiReport.startRowIndex, 4].Value = "IA(g/kg)";
            worksheetDO[semiReport.startRowIndex, 5].Value = "DBP(ml/100g)";
            worksheetDO[semiReport.startRowIndex, 6].Value = "IPH(gf)";
            worksheetDO[semiReport.startRowIndex, 9].Value = "Fines(%)";
            worksheetDO[semiReport.startRowIndex, 10].Value = "HL(%)";
            worksheetDO[semiReport.startRowIndex, 11].Value = "Sieve Residue(ppm)";
            //第二行
            worksheetDO[semiReport.startRowIndex + 1, 6].Value = "AVG";
            worksheetDO[semiReport.startRowIndex + 1, 7].Value = "High";
            worksheetDO[semiReport.startRowIndex + 1, 8].Value = "Low";
            worksheetDO[semiReport.startRowIndex + 1, 11].Value = "#35";
            worksheetDO[semiReport.startRowIndex + 1, 12].Value = "#100";
            worksheetDO[semiReport.startRowIndex + 1, 13].Value = "#325";
            //合并单元格  横向 IPH     Sieve
            Range rangeIPH = worksheetDO.Range.FromLTRB(6, semiReport.startRowIndex, 8, semiReport.startRowIndex);
            worksheetDO.MergeCells(rangeIPH);
            Range rangeSieve = worksheetDO.Range.FromLTRB(11, semiReport.startRowIndex, 13, semiReport.startRowIndex);
            worksheetDO.MergeCells(rangeSieve);
            //合并单元格 zongxiang
            int[] intArray = new int[] { 0, 1, 2, 3, 4, 5, 9, 10 };
            foreach (int i in intArray)
            {
                Range rangeUnit = worksheetDO.Range.FromLTRB(i, semiReport.startRowIndex, i, semiReport.startRowIndex + 1);
                worksheetDO.MergeCells(rangeUnit);
            }
            //标题第一行加高
            RowCollection rows = worksheetDO.Rows;
            Row row_byIndex = rows[semiReport.startRowIndex];
            row_byIndex.RowHeight = 140;
            //字体加粗  居中

            //合并Unit 下方单元格   Grade下方单元格
            Range rangeUnitDetail = worksheetDO.Range.FromLTRB(0, semiReport.startRowIndex + 2, 0, semiReport.startRowIndex + 15);
            worksheetDO[semiReport.startRowIndex + 2, 0].Value = NO + 1;
            worksheetDO[semiReport.startRowIndex + 2, 0].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheetDO[semiReport.startRowIndex + 2, 0].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;

            Range rangeGradeDetail = worksheetDO.Range.FromLTRB(1, semiReport.startRowIndex + 2, 1, semiReport.startRowIndex + 15);
            worksheetDO[semiReport.startRowIndex + 2, 1].Value = semiReport.goodsName;
            worksheetDO[semiReport.startRowIndex + 2, 1].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheetDO[semiReport.startRowIndex + 2, 1].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;

            worksheetDO.MergeCells(rangeUnitDetail);
            worksheetDO.MergeCells(rangeGradeDetail);
            //合并month下方 spec‘单元格
            Range rangeSpec = worksheetDO.Range.FromLTRB(2, semiReport.startRowIndex + 2, 2, semiReport.startRowIndex + 3);
            worksheetDO.MergeCells(rangeSpec);
            worksheetDO[semiReport.startRowIndex + 2, 2].Value = "spec'";
            worksheetDO[semiReport.startRowIndex + 2, 2].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheetDO[semiReport.startRowIndex + 2, 2].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            //合并month下方 Month‘单元格
            Range rangeMonthCurrent = worksheetDO.Range.FromLTRB(2, semiReport.startRowIndex + 4, 2, semiReport.startRowIndex + 9);
            worksheetDO.MergeCells(rangeMonthCurrent);
            worksheetDO[semiReport.startRowIndex + 4, 2].Value = startDate.ToShortDateString() + "--" + endDate.ToShortDateString();       //当前月份
            worksheetDO[semiReport.startRowIndex + 4, 2].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheetDO[semiReport.startRowIndex + 4, 2].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            //合并month下方 之前月份 ‘单元格
            Range rangeMonthBefore = worksheetDO.Range.FromLTRB(2, semiReport.startRowIndex + 10, 2, semiReport.startRowIndex + 15);
            worksheetDO.MergeCells(rangeMonthBefore);
            DateTime fristDay = MonthReportBaseSetings.GetFristDaysOfYear(startDate);
            worksheetDO[semiReport.startRowIndex + 10, 2].Value = fristDay.ToShortDateString() + "--" + endDate.ToShortDateString();
            worksheetDO[semiReport.startRowIndex + 10, 2].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            worksheetDO[semiReport.startRowIndex + 10, 2].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            //Category
            worksheetDO[semiReport.startRowIndex + 2, 3].Value = "LSL";
            worksheetDO[semiReport.startRowIndex + 3, 3].Value = "USL";
            worksheetDO[semiReport.startRowIndex + 4, 3].Value = "Count";
            worksheetDO[semiReport.startRowIndex + 5, 3].Value = "Avg.";
            worksheetDO[semiReport.startRowIndex + 6, 3].Value = "Std.";
            worksheetDO[semiReport.startRowIndex + 7, 3].Value = "Min.";
            worksheetDO[semiReport.startRowIndex + 8, 3].Value = "Max.";
            worksheetDO[semiReport.startRowIndex + 9, 3].Value = "Cpk";

            worksheetDO[semiReport.startRowIndex + 12, 3].Value = "Count";
            worksheetDO[semiReport.startRowIndex + 13, 3].Value = "Avg.";
            worksheetDO[semiReport.startRowIndex + 14, 3].Value = "Std.";
            worksheetDO[semiReport.startRowIndex + 15, 3].Value = "Min.";
            worksheetDO[semiReport.startRowIndex + 16, 3].Value = "Max.";
            worksheetDO[semiReport.startRowIndex + 17, 3].Value = "Cpk";
        }

        private void DoWriteStandard(DoMonthReport doReport)
        {
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet worksheetDO = workbook.Worksheets["DO"];
            Worksheet worksheetDO_Data = workbook.Worksheets["DO_Data"];
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_IA_ID, 4);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_DBP_ID, 5);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_IPHavg_ID, 6);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_IPHhigh_ID, 7);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_IPHlow_ID, 8);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_Fines_ID, 9);      //4-13
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_HL_ID, 10);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_SieveResidue35_ID, 11);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_SieveResidue100_ID, 12);
            DoWriteStandardDetail(worksheetDO, doReport, CommonData.TestItem_SieveResidue325_ID, 13);
        }

        private void DoWriteStandardDetail(Worksheet workSheetDO, DoMonthReport doReport, string testItemID, int columnIndex)
        {
            StandardSemi standard = doReport.standardSemiList.Find(m => m.TestItemID == testItemID);
            if (standard != null)
            {
                if (!String.IsNullOrEmpty(standard.MinValue))
                {
                    workSheetDO[doReport.startRowIndex + 2, columnIndex].Value = Convert.ToDouble(standard.MinValue);
                }
                if (!String.IsNullOrEmpty(standard.MaxValue))
                {
                    workSheetDO[doReport.startRowIndex + 3, columnIndex].Value = Convert.ToDouble(standard.MaxValue);
                }
            }




        }

        private void DoWriteBeforData(DoMonthReport semiReport, DateTime startDay, DateTime endDay)
        {
            //count 
            DateTime fristDay = MonthReportBaseSetings.GetFristDaysOfYear(startDay);
            var models = db.ReportSemiResults.Where(m => m.ReportSemi.GoodsID == semiReport.goodsID && m.ReportSemi.SamplingPlaceID == CommonData.SamplingPlace_Do_ID && m.ReportSemi.SendDate <= endDay && m.ReportSemi.SendDate >= fristDay);
            for (int i = 4; i < 14; i++)   //遍历 列
            {
                //count 
                string testItemID = CommonData.GetTestItemIdByColumnIndex(i).TestItemID;
                int count = models.Where(m => m.TestItemID == testItemID).Count();
                //avg
                List<string> avgString = models.Where(m => m.TestItemID == testItemID).Select(m => m.TestValue).ToList();
                List<double> testValueList = new List<double>();
                foreach (var item in avgString)
                {
                    if (!String.IsNullOrEmpty(item))
                    {
                        testValueList.Add(Convert.ToDouble(item));
                    }
                }
                //1思路整理   先查出来，自己写个方法再转成double
                //2 直接实用sql 语句查询
                double avg = testValueList.Average();
                //

            }

        }

        private ReportSemiResult Get(double? testValue)
        {

            return new ReportSemiResult();
        }

        private void DoDataWriteStandard(int i, Worksheet workSheet, int rowIndex, DoMonthReport semiReport)
        {
            switch (i)
            {
                case 3:    //ia
                    StandardSemi standardIA = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_IA_ID);
                    if (standardIA != null)
                    {
                        workSheet[rowIndex, i].Value = standardIA.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardIA.MaxValue;
                    }
                    break;
                case 4:
                    StandardSemi standardDBP = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_DBP_ID);
                    if (standardDBP != null)
                    {
                        workSheet[rowIndex, i].Value = standardDBP.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardDBP.MaxValue;
                    }
                    break;
                case 5:
                    StandardSemi standardIPHavg = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_IPHavg_ID);
                    if (standardIPHavg != null)
                    {
                        workSheet[rowIndex, i].Value = standardIPHavg.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardIPHavg.MaxValue;
                    }
                    break;
                case 6:
                    StandardSemi standardIPHhigh = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_IPHhigh_ID);
                    if (standardIPHhigh != null)
                    {
                        workSheet[rowIndex, i].Value = standardIPHhigh.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardIPHhigh.MaxValue;
                    }
                    break;
                case 7:
                    StandardSemi standardIPHlow = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_IPHlow_ID);
                    if (standardIPHlow != null)
                    {
                        workSheet[rowIndex, i].Value = standardIPHlow.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardIPHlow.MaxValue;
                    }
                    break;
                case 8:
                    StandardSemi standardFines = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_Fines_ID);
                    if (standardFines != null)
                    {
                        workSheet[rowIndex, i].Value = standardFines.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardFines.MaxValue;
                    }
                    break;
                case 9:
                    StandardSemi standardHL = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_HL_ID);
                    if (standardHL != null)
                    {
                        workSheet[rowIndex, i].Value = standardHL.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardHL.MaxValue;
                    }
                    break;
                case 10:
                    StandardSemi standardSieveResidue35 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_SieveResidue35_ID);
                    if (standardSieveResidue35 != null)
                    {
                        workSheet[rowIndex, i].Value = standardSieveResidue35.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardSieveResidue35.MaxValue;
                    }
                    break;
                case 11:
                    StandardSemi standardSieveResidue100 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_SieveResidue100_ID);
                    if (standardSieveResidue100 != null)
                    {
                        workSheet[rowIndex, i].Value = standardSieveResidue100.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardSieveResidue100.MaxValue;
                    }
                    break;
                case 12:
                    StandardSemi standardSieveResidue325 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_SieveResidue325_ID);
                    if (standardSieveResidue325 != null)
                    {
                        workSheet[rowIndex, i].Value = standardSieveResidue325.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardSieveResidue325.MaxValue;
                    }
                    break;
                case 13:
                    StandardSemi standardLT = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_LT_ID);
                    if (standardLT != null)
                    {
                        workSheet[rowIndex, i].Value = standardLT.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardLT.MaxValue;
                    }
                    break;
                case 14:
                    StandardSemi standardPD = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PD_ID);
                    if (standardPD != null)
                    {
                        workSheet[rowIndex, i].Value = standardPD.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPD.MaxValue;
                    }
                    break;
                case 15:
                    StandardSemi standardPH = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PH_ID);
                    if (standardPH != null)
                    {
                        workSheet[rowIndex, i].Value = standardPH.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPH.MaxValue;
                    }
                    break;
                case 16:
                    StandardSemi standardPSD10 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSD10_ID);
                    if (standardPSD10 != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSD10.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSD10.MaxValue;
                    }
                    break;
                case 17:
                    StandardSemi standardPSD18 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSD18_ID);
                    if (standardPSD18 != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSD18.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSD18.MaxValue;
                    }
                    break;
                case 18:
                    StandardSemi standardPSD35 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSD35_ID);
                    if (standardPSD35 != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSD35.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSD35.MaxValue;
                    }
                    break;
                case 19:
                    StandardSemi standardPSD60 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSD60_ID);
                    if (standardPSD60 != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSD60.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSD60.MaxValue;
                    }
                    break;
                case 20:
                    StandardSemi standardPSD120 = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSD120_ID);
                    if (standardPSD120 != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSD120.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSD120.MaxValue;
                    }
                    break;
                case 21:
                    StandardSemi standardPSDPAN = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_PSDPAN_ID);
                    if (standardPSDPAN != null)
                    {
                        workSheet[rowIndex, i].Value = standardPSDPAN.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardPSDPAN.MaxValue;
                    }
                    break;
                case 22:
                    StandardSemi standardTint = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_Tint_ID);
                    if (standardTint != null)
                    {
                        workSheet[rowIndex, i].Value = standardTint.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardTint.MaxValue;
                    }
                    break;
                case 23:
                    StandardSemi standardCDBP = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_CDBP_ID);
                    if (standardCDBP != null)
                    {
                        workSheet[rowIndex, i].Value = standardCDBP.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardCDBP.MaxValue;
                    }
                    break;
                case 24:
                    StandardSemi standardN2SA = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_N2SA_ID);
                    if (standardN2SA != null)
                    {
                        workSheet[rowIndex, i].Value = standardN2SA.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardN2SA.MaxValue;
                    }
                    break;
                case 25:
                    StandardSemi standardAsh = semiReport.standardSemiList.Find(m => m.TestItemID == CommonData.TestItem_Ash_ID);
                    if (standardAsh != null)
                    {
                        workSheet[rowIndex, i].Value = standardAsh.MinValue;
                        workSheet[rowIndex + 1, i].Value = standardAsh.MaxValue;
                    }
                    break;
            }
        }

        private void DoDataExportDataFromDataBase(DateTime startDate, DateTime endDate)
        {
           // ReportSemiModel
            IWorkbook workbook = spreadsheetControl1.Document;
            Worksheet TestSheet2 = workbook.Worksheets.Add("TestSheet2");


            string sql = "SELECT [Project11].[GoodsName] AS [Grade], [Project11].[SamplingPlaceName] AS [SamplingPlaceName], [Project11].[ProductionLineName] AS [ProductionLineName], [Project11].[ProductionGroupName] AS [ProductionGroupName], [Project11].[SendDate] AS [SendDate], [Project11].[Remark] AS [Remark], [Project11].[EmpName] AS [EmpName], [Project11].[TestValue] AS IA, [Project11].[TestValue1] AS DBP, [Project11].[TestValue2] AS [IPH(AVG)], [Project11].[TestValue3] AS [IPH(High)], [Project11].[TestValue4] AS [IPH(Low)], [Project11].[TestValue5] AS [Fines], [Project11].[TestValue6] AS [HL], [Project11].[TestValue7] AS [Sieve Residue#35], [Project11].[TestValue8] AS [Sieve Residue#100], [Project11].[TestValue9] AS [Sieve Residue#325] FROM ( SELECT [Filter1].[ReportSemiID] AS [ReportSemiID], [Filter1].[GoodsID1] AS [GoodsID], [Filter1].[SamplingPlaceID1] AS [SamplingPlaceID], [Filter1].[ProductionLineID1] AS [ProductionLineID], [Filter1].[ProductionGroupID1] AS [ProductionGroupID], [Filter1].[SendDate] AS [SendDate], [Filter1].[Remark1] AS [Remark], [Filter1].[LoginUserID1] AS [LoginUserID], [Filter1].[AddDateTime] AS [AddDateTime], [Filter1].[GoodsName] AS [GoodsName], [Filter1].[SamplingPlaceName] AS [SamplingPlaceName], [Filter1].[ProductionLineName] AS [ProductionLineName], [Filter1].[ProductionGroupName] AS [ProductionGroupName], [Filter1].[EmpName] AS [EmpName], [Limit1].[TestValue] AS [TestValue], [Limit2].[TestValue] AS [TestValue1], [Limit3].[TestValue] AS [TestValue2], [Limit4].[TestValue] AS [TestValue3], [Limit5].[TestValue] AS [TestValue4], [Limit6].[TestValue] AS [TestValue5], [Limit7].[TestValue] AS [TestValue6], [Limit8].[TestValue] AS [TestValue7], [Limit9].[TestValue] AS [TestValue8], [Limit10].[TestValue] AS [TestValue9], 1 AS [C1] FROM (SELECT [Extent1].[ReportSemiID] AS [ReportSemiID], [Extent1].[GoodsID] AS [GoodsID1], [Extent1].[SamplingPlaceID] AS [SamplingPlaceID1], [Extent1].[ProductionLineID] AS [ProductionLineID1], [Extent1].[ProductionGroupID] AS [ProductionGroupID1], [Extent1].[SendDate] AS [SendDate], [Extent1].[Remark] AS [Remark1], [Extent1].[LoginUserID] AS [LoginUserID1], [Extent1].[AddDateTime] AS [AddDateTime], [Extent2].[GoodsName] AS [GoodsName], [Extent3].[SamplingPlaceName] AS [SamplingPlaceName], [Extent4].[ProductionLineName] AS [ProductionLineName], [Extent5].[ProductionGroupName] AS [ProductionGroupName], [Extent6].[EmpName] AS [EmpName] FROM [dbo].[ReportSemis] AS [Extent1] INNER JOIN [dbo].[Goods] AS [Extent2] ON [Extent1].[GoodsID] = [Extent2].[GoodsID] INNER JOIN [dbo].[SamplingPlaces] AS [Extent3] ON [Extent1].[SamplingPlaceID] = [Extent3].[SamplingPlaceID] INNER JOIN [dbo].[ProductionLines] AS [Extent4] ON [Extent1].[ProductionLineID] = [Extent4].[ProductionLineID] INNER JOIN [dbo].[ProductionGroups] AS [Extent5] ON [Extent1].[ProductionGroupID] = [Extent5].[ProductionGroupID] INNER JOIN [dbo].[LoginUsers] AS [Extent6] ON [Extent1].[LoginUserID] = [Extent6].[LoginUserID] WHERE ( [Extent1].[GoodsID]= @GoodsID ) AND ([Extent1].[SendDate] <= @EndDate) AND ([Extent1].[SendDate] >= @StartDate) AND (@Point = [Extent1].[SamplingPlaceID]) ) AS [Filter1] OUTER APPLY (SELECT TOP (1) [Extent7].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent7] WHERE ([Filter1].[ReportSemiID] = [Extent7].[ReportSemiID]) AND (N'1' = [Extent7].[TestItemID]) ) AS [Limit1] OUTER APPLY (SELECT TOP (1) [Extent8].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent8] WHERE ([Filter1].[ReportSemiID] = [Extent8].[ReportSemiID]) AND (N'7' = [Extent8].[TestItemID]) ) AS [Limit2] OUTER APPLY (SELECT TOP (1) [Extent9].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent9] WHERE ([Filter1].[ReportSemiID] = [Extent9].[ReportSemiID]) AND (N'46' = [Extent9].[TestItemID]) ) AS [Limit3] OUTER APPLY (SELECT TOP (1) [Extent10].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent10] WHERE ([Filter1].[ReportSemiID] = [Extent10].[ReportSemiID]) AND (N'8' = [Extent10].[TestItemID]) ) AS [Limit4] OUTER APPLY (SELECT TOP (1) [Extent11].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent11] WHERE ([Filter1].[ReportSemiID] = [Extent11].[ReportSemiID]) AND (N'30' = [Extent11].[TestItemID]) ) AS [Limit5] OUTER APPLY (SELECT TOP (1) [Extent12].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent12] WHERE ([Filter1].[ReportSemiID] = [Extent12].[ReportSemiID]) AND (N'9' = [Extent12].[TestItemID]) ) AS [Limit6] OUTER APPLY (SELECT TOP (1) [Extent13].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent13] WHERE ([Filter1].[ReportSemiID] = [Extent13].[ReportSemiID]) AND (N'10' = [Extent13].[TestItemID]) ) AS [Limit7] OUTER APPLY (SELECT TOP (1) [Extent14].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent14] WHERE ([Filter1].[ReportSemiID] = [Extent14].[ReportSemiID]) AND (N'12' = [Extent14].[TestItemID]) ) AS [Limit8] OUTER APPLY (SELECT TOP (1) [Extent15].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent15] WHERE ([Filter1].[ReportSemiID] = [Extent15].[ReportSemiID]) AND (N'13' = [Extent15].[TestItemID]) ) AS [Limit9] OUTER APPLY (SELECT TOP (1) [Extent16].[TestValue] AS [TestValue] FROM [dbo].[ReportSemiResults] AS [Extent16] WHERE ([Filter1].[ReportSemiID] = [Extent16].[ReportSemiID]) AND (N'14' = [Extent16].[TestItemID]) ) AS [Limit10] ) AS [Project11] ORDER BY [Project11].[SendDate] ASC";
            
            SqlParameter SqlParameterGoodsID = new System.Data.SqlClient.SqlParameter("@GoodsID", "5");
            SqlParameter SqlParameterStartDate = new System.Data.SqlClient.SqlParameter("@StartDate", startDate);
            SqlParameter SqlParameterEndDate = new System.Data.SqlClient.SqlParameter("@EndDate", endDate);
            SqlParameter SqlParameterPoint = new System.Data.SqlClient.SqlParameter("@Point", CommonData.SamplingPlace_Do_ID);
            SqlParameter[] parameters = new SqlParameter[] { SqlParameterGoodsID, SqlParameterStartDate, SqlParameterEndDate, SqlParameterPoint };
          

            DataTable table = LimsDbHelper.SqlQueryForDataTatable(db.Database, sql, parameters);


            TestSheet2.Import(table, true, 1, 1);

         
           


        }
        #endregion
    }








    class RawMonthReport
    {
        public int startRowIndex { get; set; }
        public int endRowIndex { get; set; }
        public List<StandardRaw> standardRawList { get; set; }
        public List<ReportRawModel> reportList { get; set; }
        public string goodsName { get; set; }
    }

    class DoMonthReport
    {
        public int startRowIndex { get; set; }
        public int endRowIndex { get; set; }
        public List<StandardSemi> standardSemiList { get; set; }
        public List<ReportSemiModel> reportList { get; set; }
        public string goodsName { get; set; }
        public string goodsID { get; set; }

        public int dataStartRow { get; set; }
        public int dataEndRow { get; set; }
    }




}
